/*
 * validadoresCambios.java
 *
 * Created on 17 de septiembre de 2007, 11:49 AM
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package score.utils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author analista4
 */
public class validadoresCambios {
    
    /** Creates a new instance of validadoresCambios */
    private DataSource datasource;
    
    public validadoresCambios() {
        try{
            // Look up the JNDI data source only once at init time
            Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
            datasource = (DataSource) envCtx.lookup("jdbc/score");
        }catch(NamingException ex){
            ex.printStackTrace();
            
        }
    }
    
    private Connection getConnection() throws SQLException {
        return datasource.getConnection();
    }
    
    public String validandoQueNoEsteEnDiseno(String codigoVariable){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            //Validando que no este en la sct0026
            rs = sqlStatement.executeQuery("SELECT CDGODSNOSLCTUD FROM SCR.SCT0026 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0' FETCH FIRST 1 ROW ONLY  WITH UR");
            if(rs.next())
                return rs.getString(1);
        }catch(SQLException ex){
            return "ERR"+ex.getErrorCode();
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
    
     public String validandoQueNoEsteEnRelacion(String codigoVariable){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            //Validando que no este en la sct0026
            rs = sqlStatement.executeQuery("SELECT CDGOVRBLEPDRE FROM SCR.SCT0032 WHERE CDGOVRBLEHJO='"+codigoVariable+"' AND ESTDO='0' FETCH FIRST 1 ROW ONLY WITH UR");
            if(rs.next())
                return rs.getString(1);
        }catch(SQLException ex){
            return "ERR"+ex.getErrorCode();
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
     
     public String validandoQueNoEsteEnCalculadas(String codigoVariable){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            //Validando que no este en la sct0026
            rs = sqlStatement.executeQuery("SELECT CDGOVRBLECLCLDA FROM SCR.SCT0027 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0'  FETCH FIRST 1 ROW ONLY WITH UR");
            if(rs.next())
                return rs.getString(1);
        }catch(SQLException ex){
            return "ERR"+ex.getErrorCode();
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
    
    public String validandoRangos(String codigoVariable,String signo,String valorVariable,String tipoDato){
        Connection conn = null ;
        Statement sqlStatement=null;
        PreparedStatement prStatement = null;
        ResultSet rs =null;
        /*Este metodo se encarga de verificar que los nuevos rangos no se sobrepasen con la informacion ya existente en las tablas: 
         *(sct0029 solicitudes)-(sct0036,sct0037,sct0038) clientes, sct0040 politicas
         */
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            if(tipoDato.equals("0")){
                rs = sqlStatement.executeQuery("SELECT  'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND LONG(VLORVRBLE)"+signo+"LONG("+valorVariable+") AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND LONG(VLORVRBLE)"+signo+"LONG("+valorVariable+") AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND LONG(VLORVRBLE)"+signo+"LONG("+valorVariable+") AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND LONG(VLORVRBLE)"+signo+"LONG("+valorVariable+") AND ESTDO='0' UNION SELECT 'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE CDGOVRBLE='"+codigoVariable+"' AND (LONG(VLRVRBLEMDLOPLTCA)"+signo+"LONG("+valorVariable+") OR LONG(VLRVRBLESLCTUD)"+signo+"LONG("+valorVariable+")) FETCH FIRST 1 ROWS ONLY  WITH UR");
                if(rs.next())
                    return rs.getString(1)+rs.getString(2);
                
            }
            if(tipoDato.equals("1")){
                rs = sqlStatement.executeQuery("SELECT  'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND DECIMAL(VLORVRBLE,31,6)"+signo+"DECIMAL("+valorVariable+") AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND DECIMAL(VLORVRBLE,31,6)"+signo+"DECIMAL("+valorVariable+") AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND DECIMAL(VLORVRBLE,31,6)"+signo+"DECIMAL("+valorVariable+") AND ESTDO='0' UNION  SELECT  'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND DECIMAL(VLORVRBLE,31,6)"+signo+"DECIMAL("+valorVariable+") AND ESTDO='0' UNION SELECT 'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE CDGOVRBLE='"+codigoVariable+"' AND (DECIMAL(VLRVRBLEMDLOPLTCA,31,6)"+signo+"DECIMAL("+valorVariable+") OR DECIMAL(VLRVRBLESLCTUD,31,6)"+signo+"DECIMAL("+valorVariable+")) FETCH FIRST 1 ROWS ONLY WITH UR");
                if(rs.next())
                    return rs.getString(1)+rs.getString("2");
            }
            
            if(tipoDato.equals("3")){
                    rs=sqlStatement.executeQuery("SELECT  'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND DATE(VLORVRBLE)"+signo+"DATE('"+valorVariable+"') AND ESTDO='0'  UNION SELECT  'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND DATE(VLORVRBLE)"+signo+"DATE('"+valorVariable+"') AND ESTDO='0' UNION  SELECT  'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND DATE(VLORVRBLE)"+signo+"DATE('"+valorVariable+"') AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND DATE(VLORVRBLE)"+signo+"DATE('"+valorVariable+"') AND ESTDO='0' UNION SELECT  'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE CDGOVRBLE='"+codigoVariable+"' AND (DATE(VLRVRBLEMDLOPLTCA)"+signo+"DATE('"+valorVariable+"') OR DATE(VLRVRBLESLCTUD)"+signo+"DATE('"+valorVariable+"'))   FETCH FIRST 1 ROWS ONLY WITH UR"); 
                if(rs.next())
                    return rs.getString(1)+rs.getString(2);
            }
        }catch(SQLException ex){
            return "ERR"+ex;
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
    /** Este metodo se encarga de validar que la variable no este en uso en ninguna de las siguientes tablas: 
     * sct0040(politicas),clientes(sct0038,sct0037,sct0036), y solicitudes (sct0029)
     */
    public String validandoQueNoEsteEnSolsCliPol(String codigoVariable){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            //SALIDA 'SOL'NUMEROSOL -- 'NIT'NITCLIENTE -- 'POL'SOLICITUDCONPOLITICA 
            //TABLAS SCT0029, SCT0038,SCT0037,SCT0036, SCT0040
            rs = sqlStatement.executeQuery("SELECT 'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0' UNION SELECT 'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0'  UNION SELECT 'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0' UNION SELECT 'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0' UNION SELECT 'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE CDGOVRBLE='"+codigoVariable+"' FETCH FIRST 1 ROWS ONLY WITH UR");
            if(rs.next())
                return rs.getString(1)+rs.getString(2);
            
        }catch(SQLException ex){
            return "ERR"+ex.getErrorCode();
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
    
   
    public String validandoQueNoEsteEnReporte(String codigoVariable){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            rs = sqlStatement.executeQuery("SELECT VLORVRBLE, CDGORPRTE FROM SCR.SCT0035 WHERE CDGOVRBLE='"+codigoVariable+"' AND ESTDO='0' WITH UR");
            if(rs.next())
                return rs.getString(2);
        }catch(SQLException ex){
            return "ERROR"+ex;
        } finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
    
    
    public String validandoCambioLongitud(String codigoVariable,String tipoDato,long longitud){
        Connection conn = null ;
        Statement sqlStatement=null;
        Statement sqlStatement1=null;
        ResultSet rs =null;
        try{
            //Verificando que la variable pueda ser actualizada
            //Primero hay que ver si la variable puede ser cambiada de longitud
            conn = getConnection();
            conn.setAutoCommit(false) ;
            sqlStatement = conn.createStatement();
            if(tipoDato.equals("0")||(tipoDato.equals("2"))){
                //Revisa en la (sct0029)sols, (sct0038,sct0037,sct0036)clientes y (sct0040)politicas
                rs = sqlStatement.executeQuery("SELECT  'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND  LENGTH(VLORVRBLE)>"+longitud+" AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND  LENGTH(VLORVRBLE)>"+longitud+" AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND  LENGTH(VLORVRBLE)>"+longitud+" AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND  LENGTH(VLORVRBLE)>"+longitud+" AND ESTDO='0' UNION SELECT 'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE LENGTH(VLRVRBLEMDLOPLTCA)>"+longitud+" AND  LENGTH(VLRVRBLESLCTUD)>"+longitud+" FETCH FIRST 1 ROWS ONLY WITH UR");
                if(rs.next())
                    return rs.getString(1)+rs.getString(2);
            }
            if(tipoDato.equals("1")){
                //Revisa en la (sct0029)sols, (sct0038,sct0037,sct0036)clientes y (sct0040)politicas
                rs = sqlStatement.executeQuery("SELECT  'SOL',NMROSLCTUD FROM SCR.SCT0029 WHERE CDGOVRBLE='"+codigoVariable+"' AND LENGTH(VLORVRBLE)>"+longitud+"-1 AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0038 WHERE CDGOVRBLE='"+codigoVariable+"' AND LENGTH(VLORVRBLE)>"+longitud+"-1 AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0037 WHERE CDGOVRBLE='"+codigoVariable+"' AND LENGTH(VLORVRBLE)>"+longitud+"-1 AND ESTDO='0' UNION SELECT  'NIT',NIT FROM SCR.SCT0036 WHERE CDGOVRBLE='"+codigoVariable+"' AND LENGTH(VLORVRBLE)>"+longitud+"-1 AND ESTDO='0' UNION SELECT 'POL',NMROSLCTUD FROM SCR.SCT0040 WHERE LENGTH(VLRVRBLEMDLOPLTCA)>"+longitud+"-1 AND LENGTH(VLRVRBLESLCTUD)>"+longitud+"-1 FETCH FIRST 1 ROWS ONLY WITH UR");
                if(rs.next())
                    return rs.getString(1)+rs.getString(2);
                
            }
        }catch(SQLException ex){
            return "Error SQL : "+ex;
        }finally{
            if(sqlStatement!=null)
                try{sqlStatement.close();} catch (SQLException e) {}
            if(rs!=null)
                try{rs.close();} catch (SQLException e) {}
            if (conn != null)
                try{conn.close();} catch (SQLException e) {}
        }
        return "PASO";
    }
}
